<?php
    // Created By : Noan Babao
    // B.S. Computer Science - De La Salle Canlubang
    // September 28, 2011

    // IN: origin
    // OUT: Tagged by user Main document list 

// Start - Checker for those users who will just go to the page by typing directly in the url.
if ($_POST) {
    // INCLUDES CONFIGURATION VARIABLES FOR DATABASE ACCESS
    include('../includes/siteConfig.php');
    
    
    // CONNECT TO DATABASE
    $connect = mysql_connect($hostName,$rootName,$dBasePassword) or die ('Unable to connect!');
    mysql_select_db($dBaseName) or die ('Unable to select database!');
    
    // GET VARIABLES VIA POST METHOD
    $user = $_POST['user'];
    $action =$_POST['action'];
    $receiveFlag= $_POST['receiveFlag'];
    
    // QUERY - GETS ALL DOCUMENTS BASED ON DOCUMENT TYPE
    
    if ( $receiveFlag != "( receiveFlag = 'Pending' AND orgFlag = 'Internal')"){
      // dettached attachments
        $query =
           " 
            (   SELECT MD.mainDocumentId documentId, firstName, type , label, action, DS.documentSetupId documentSetupId
                FROM Log, MainDocument MD, DocumentSetup DS, Document_Label DL, Label L, Account
                WHERE Log.documentId = MD.mainDocumentId 
                      AND Log.userId =  '$user'
                      AND DS.documentSetupId = MD.documentSetupId
                      AND MD.mainDocumentId = DL.documentId
                      AND L.labelId = DL.labelId
                      AND mainLabel =  '1'
                      AND Log.activity =  'Received'
                      AND Log.logDateTime = ( 
                                             SELECT MAX( logDateTime ) 
                                             FROM Log
                                             WHERE MD.mainDocumentId = documentId
                                            ) 
                      AND MD.originId = Account.userId
            )
        
        UNION
        ( SELECT MD.mainDocumentId documentId, firstName, type , label, action, DS.documentSetupId documentSetupId  
                FROM Log, MainDocument MD, DocumentSetup DS, Document_Label DL, Label L, Account
                WHERE Log.documentId = MD.mainDocumentId 
                      AND Log.userId =  '$user'
                      AND DS.documentSetupId = MD.documentSetupId
                      AND MD.mainDocumentId = DL.documentId
                      AND L.labelId = DL.labelId
                      AND mainLabel =  '1'
                      AND Log.activity =  'Received'
                      AND MD.action = 'Cancelled'
                      AND Log.logDateTime = ( 
                                             SELECT MAX( logDateTime ) 
                                             FROM Log
                                             WHERE MD.mainDocumentId = documentId AND Log.activity = 'Received'
                                            ) 
                      AND MD.originId = Account.userId
        )
        UNION

            (SELECT  DISTINCT SA.documentId documentId,firstName, type, label, receiveFlag, DS.documentSetupId documentSetupId
            FROM Sent_Attachment SA , Account_Dept_Pos ADP, Attachment A, DocumentSetup DS, Account, Document_Label DL, Label L
            WHERE   ADP.userId = '$user' 
                    AND ADP.deptPosId = SA.receiverId
                    AND SA.documentId = A.attachmentId
                    AND A.status = 'Active'
                    AND A.documentSetupId = DS.documentSetupId
                    AND Account.userId = SA.senderId
                    AND $receiveFlag 
                    AND SA.documentId= DL.documentId
                    AND L.labelId = DL.labelId

            group by documentId)
        
            UNION
            (
             SELECT a.documentId documentId, accountDetached.firstName firstName, type, label, a.activity action, DS.documentSetupId documentSetupId
             FROM Log a ,  Attachment , DocumentSetup DS, Document_Label DL, Label L, Account accountDetached
             WHERE EXISTS (SELECT *
                          FROM Log b 
                          WHERE a.documentId= b.documentId AND activity = 'detached' 
                          ) 

                  AND a.logDateTime = ( SELECT MAX(c.logDateTime)
                                  FROM Log c
                                  WHERE a.documentId = c.documentId
                                )


                  AND a.logId = (SELECT MAX(logId) FROM Log c
                                 WHERE a.documentId= c.documentId)
                  AND a.activity = 'detached'
                  AND a.documentId = Attachment.attachmentId
                  AND Attachment.Status = 'Active'
                  AND a.userId = '$user'
                  AND L.labelId = DL.labelId
                  AND Attachment.documentSetupId = DS.documentSetupId
                  AND a.documentId = DL.documentId
                  AND accountDetached.userId = (SELECT origin.userId
                                        FROM Log origin
                                        WHERE a.documentId = origin.documentId AND origin.activity = 'Tagged'
                                       )

            group by documentId
               
            )
                 order by label";
    }
    else{
           $query = 
        "
        (SELECT MD.mainDocumentId documentId, firstName, type, label, action, DS.documentSetupId documentSetupId
            FROM Account , Account_Dept_Pos ADP, RouteStep_Dept_Pos RSDP, MainDocument MD, DocumentSetup DS , Document_Label DL , Label L

            WHERE   ADP.userId = '$user' 
                    AND Account.userId = MD.originId 	
                    AND RSDP.deptPosId = ADP.deptPosID 
                    AND MD.currentStepId = RSDP.stepId 
                    AND MD.status= 'Active' 
                    AND $action
                    AND DS.documentSetupId =MD.documentSetupId
                    AND MD.mainDocumentId = DL.documentId
                    AND L. labelId = DL.labelId
                    AND mainLabel='1'
                    AND ADP.status='Active'
            ORDER BY label asc
            )

            UNION

            (SELECT  DISTINCT SA.documentId documentId,firstName, type, label, receiveFlag, DS.documentSetupId documentSetupId
            FROM Sent_Attachment SA , Account_Dept_Pos ADP, Attachment A, DocumentSetup DS, Account, Document_Label DL, Label L
            WHERE   ADP.userId = '$user' 
                    AND ADP.deptPosId = SA.receiverId
                    AND SA.documentId = A.attachmentId
                    AND A.status = 'Active'
                    AND A.documentSetupId = DS.documentSetupId
                    AND Account.userId = SA.senderId
                    AND $receiveFlag 
                    AND SA.documentId= DL.documentId
                    AND L.labelId = DL.labelId
                    AND ADP.status = 'Active'
            group by documentId)
    UNION

    (SELECT MD.mainDocumentId documentId, firstName, type, label, action, DS.documentSetupId documentSetupId
            FROM Account , MainDocument MD, DocumentSetup DS , Document_Label DL 
          , Label L , (SELECT stepId
                       FROM RouteStep
                       WHERE subRouteId IS NULL AND stepNumber = '1') as firstRouteStep

            WHERE   MD.originId = '$user'
                    AND  Account.userId = MD.originId
                    AND MD.status= 'Active' 
                    AND $action
                    AND DS.documentSetupId = MD.documentSetupId
                    AND MD.mainDocumentId = DL.documentId
                    AND L. labelId = DL.labelId
                    AND mainLabel='1'
                    AND MD.currentStepId = firstRouteStep.stepId
            ORDER BY label asc
            )
            
        ";
    
    }
    
    
    $result = mysql_query($query) or die ('Error in query: $query. ' . mysql_error());
    
    if (mysql_num_rows($result) > 0)
    {
        // SAVE QUERY RESULTS TO AN ARRAY > $row   
        while ($row = mysql_fetch_array($result)) {
            $jsondata = array();
            $jsondata['documentId'] = $row['documentId'];
            $jsondata['type'] = $row['type'];
            $jsondata['tag'] = $row['label'];
            $jsondata['name'] = $row['firstName'];
            $jsondata['status'] = $row['action'];
            $jsondata['documentSetupId'] = $row['documentSetupId'];
            $feed[] = $jsondata;
            
            error_log($jsondata['documentId']);
        }
        
        echo json_encode($feed);
        
    }

    else {
        echo json_encode(0);
    }

    
    mysql_free_result($result);
    mysql_close($connect);
}   // End - Checker for those users who will just go to the page by typing directly in the url.
else {
 echo "You are not authorized to view this page. This incident will be reported immediately.";
}
?>

